﻿using System;
using System.Collections.Generic;
using System.Text;
using QuanLyKhachSan.DTO;
using System.Data;

namespace QuanLyKhachSan.DAO
{
    public class KhachHangDAO
    {
        // Them khach hang
        public static bool Insert(KhachHangDTO khachHangdto)          
        {
            //int kq;
            //Dictionary<string, object> para = new Dictionary<string, object>();
            //para.Add("hoKH", khachHangdto.HoKhachHang);
            //para.Add("tenLot",khachHangdto.TenLot);
            //para.Add("tenKH",khachHangdto.TenKhachHang);
            //para.Add("maLoaiKH",khachHangdto.MaLoaiKH);
            //para.Add("diaChi",khachHangdto.DiaChi);
            //para.Add("CMND",khachHangdto.CMND1);

            //DataAccessHelper help = new SqlDataAccessHelper();
            //kq = help.ExecuteNonProcedure("sp_InsertKhachHang", para);
            //if (kq > 0)//neu so dong bi thay doi >0
            //{
            //    return true;
            //}
            //return false;
            string sql = "INSERT INTO KhachHang VALUES (N'"+khachHangdto.HoKhachHang +"',N'"+khachHangdto.TenLot +"',N'"+khachHangdto.TenKhachHang +"',"+khachHangdto.MaLoaiKH +",N'"+khachHangdto.DiaChi +"','"+khachHangdto.CMND1 +"',0)";
            DataAccessHelper s = new SqlDataAccessHelper();
            int kq = s.ExecuteNonQuery1(sql);
            if (kq < 0)
                return false;
            return true;
        }

        // Xoa khach hang
        public static bool Delete(int maKhachHang)
        {
            string sqlTK = "Select maKhachHang from ChiTietPhieuThue where maKhachHang=" + maKhachHang ;
            DataTable dt = new DataTable();
            DataAccessHelper s = new SqlDataAccessHelper();
            dt = s.ExecuteQuery(sqlTK);

            string sqlTK1 = "Select maKhachHang from HoaDon where maKhachHang=" + maKhachHang ;
            DataTable dt1 = new DataTable();
            dt1 = s.ExecuteQuery(sqlTK1);

            string sql = "UPDATE KhachHang SET danhDau=1 where maKhachHang" + maKhachHang ;
            if (dt.Rows.Count <= 0 && dt1.Rows.Count <= 0)
            {
                sql = "DELETE from KhachHang where maKhachHang=" + maKhachHang ;
            }
            int kq = s.ExecuteNonQuery1(sql);
            if (kq < 0)
                return false;
            return true;
        }

        // Cap nhat thong tin khach hang
        public static bool Update(KhachHangDTO khachHangdto)
        {
            string sql = "UPDATE KhachHang SET hoKhachHang=N'"+khachHangdto.HoKhachHang +"',tenLot=N'"+khachHangdto.TenLot +"',tenKhachHang=N'"+khachHangdto.TenKhachHang +"',maLoaiKH="+khachHangdto.MaLoaiKH +",diaChi=N'"+khachHangdto.DiaChi +"',CMND='"+khachHangdto.CMND1 +"' where maKhachHang="+khachHangdto.MaKhachHang ;
            DataAccessHelper s = new SqlDataAccessHelper();
            int kq = s.ExecuteNonQuery1(sql);
            if (kq < 0)
                return false;
            return true;
        }

        // Lay danh sach khach hang
        public static DataTable LayDanhSachKH()
        {
            DataTable dt = new DataTable();
            string sql = "Select * from KhachHang where danhDau=0";
            DataAccessHelper s = new SqlDataAccessHelper();
            dt = s.ExecuteQuery(sql);
            return dt;
        }

        // Lay danh sach khach hang theo maLoaiKhach
        public static DataTable LayDanhSachKH(int maLoaiKhach)
        {
            DataTable dt = new DataTable();
            string sql = "Select [MãKháchHàng]=maKhachHang,[Họ]=hoKhachHang,[TênLót]=tenLot,[TênKháchHàng]=tenKhachHang,[MãLoạiKH]=maLoaiKH,[ĐịaChỉ]=diaChi,CMND from KhachHang where danhDau=0";
            DataAccessHelper s = new SqlDataAccessHelper();
            dt = s.ExecuteQuery(sql);
            return dt;
        }
        //lay danh sach khach hang theo ten
        public static DataTable LayDSKhachHangTheoTen(string tenKH)
        {
            DataTable dt = new DataTable();
             //Dung proc
            Dictionary<string, object> para = new Dictionary<string, object>();
            para.Add("tenKH", tenKH);
            DataAccessHelper help = new SqlDataAccessHelper();
            dt = help.ExecuteProcedure("sp_SelectKhachHangTheoTen", para);
            return dt;
            
        }
        // Lay danh sach khach hang theo ten
        public static DataTable LayDanhSachKhachHangTheoTen(string tenKH)
        {
            //// Cau truy van
            DataTable dt = new DataTable();
            string sql = "Select [MãKháchHàng]=maKhachHang,[Họ]=hoKhachHang,[TênLót]=tenLot,[TênKháchHàng]=tenKhachHang,[MãLoạiKH]=maLoaiKH,[ĐịaChỉ]=diaChi,CMND from KhachHang where tenKhachHang like N'%" + tenKH + "%' and danhDau=0";
            DataAccessHelper s = new SqlDataAccessHelper();
            dt = s.ExecuteQuery(sql);
            return dt;
        }
        //Lay Khach Hang Moi Them 
        public static DataTable LayKhachHangMoiThem()
        {
            DataTable dt = new DataTable();

            Dictionary<string, object> para = new Dictionary<string, object>();
            DataAccessHelper help = new SqlDataAccessHelper();
            dt = help.ExecuteProcedure("sp_SelectKhachHangMoiThem", para);

            return dt;

        }
    }
}
